package cn.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import cn.dao.IUserDao;
import cn.entity.User;
import cn.util.JdbcUtil;
/**
 * 持久层
 * @author 许明远   2017-2-8 23:00(更新模糊查询)
 *
 */
public class UserDao implements IUserDao {

	private NamedParameterJdbcTemplate namedjdbctemp = new NamedParameterJdbcTemplate(
			JdbcUtil.getComboPooledDataSource());

	public void add(User user) throws Exception {
		String sql = " insert into user(`u_name` ,`u_remarks`,`u_state`,`u_createtime`,`u_lastonline`,`u_type`)"
				+ " values(:u_name,:u_remarks,:u_state,:u_createtime,:u_lastonline,:u_type)";
		SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
		KeyHolder keyholder = new GeneratedKeyHolder();
		namedjdbctemp.update(sql, ps, keyholder);
	}
	public void update(User user) throws Exception {
		String sql = " update user set u_name=:u_name , u_remarks=:u_remarks , u_state=:u_state , u_createtime=:u_createtime ,"
				+ " u_lastonline=:u_lastonline,u_type=:u_type  where u_id=:u_id ";
		SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
		namedjdbctemp.update(sql, ps);
	}

	public void delete(int u_id) throws Exception {
		String sql = "delete from user where u_id =:id";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("id", u_id);
		namedjdbctemp.update(sql, map);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public User findOne(User user) throws Exception {
		String sql = "select u_id as u_id,u_name as u_name,u_remarks as u_remarks,u_state as u_state ," +
				"u_createtime as u_createtime , u_lastonline as u_lastonline , u_type as u_type from user where u_id=:u_id";
		SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
		return (User) namedjdbctemp.queryForObject(sql, ps,
				new BeanPropertyRowMapper(User.class));
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<User> findAllRecord(int start, int end) throws Exception {
		String sql = "select u_id as u_id,u_name as u_name,u_remarks as u_remarks,u_state as u_state ," +
				"u_createtime as u_createtime , u_lastonline as u_lastonline , u_type as u_type from user  limit ?,?";
		List<User> list = namedjdbctemp.getJdbcOperations().query(sql,
				new BeanPropertyRowMapper(User.class),start,end);
		return list;
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<User> findByCondition(int start, int end, User user) {
		String sql = "select u_id as u_id,u_name as u_name,u_remarks as u_remarks,u_state as u_state ," +
				"u_createtime as u_createtime , u_lastonline as u_lastonline , u_type as u_type from user  " ;
				if(user !=null){
					sql+= " where ";
				if(user.getU_name()!=null ){
					if(user.getU_name().trim().length()>0){
						sql+=" u_name like '%"+user.getU_name()+"%' and" ;
					}
				}
			    if(user.getU_id()!=null && user.getU_id()>0){
			    	   sql+=" u_id like '%"+user.getU_id()+"%' and" ;	
				}
				if(user.getU_type()!=null && user.getU_type()>=0){
					sql+=" u_type = "+user.getU_type()+" and";
				 }
				// 去掉最后的and
				sql = sql.substring(0, sql.length()-3);
				}
				sql+= " limit ?,?";
				System.out.println(sql);
		List<User> list = namedjdbctemp.getJdbcOperations().query(sql,
				new BeanPropertyRowMapper(User.class),start,end);
		return list;
	}

	public List<User> findByCreateTime(int start, int end, String starTime,String endTime) {
		String sql = "select u_id as u_id,u_name as u_name,u_remarks as u_remarks,u_state as u_state ," +
				"u_createtime as u_createtime , u_lastonline as u_lastonline , u_type as u_type from user  where " ;
				sql+=" DATE_FORMAT(u_createtime,'%Y-%m-%d') between ? and ? " ;
				sql+= " limit ?,?";
		List<User> list = namedjdbctemp.getJdbcOperations().query(sql,
				new BeanPropertyRowMapper<User>(User.class),starTime,endTime,start,end);
		return list;
	}
	@SuppressWarnings("deprecation")
	public int getAllRecord(User user) throws Exception {
		String sql = "select count(*) from user";
		if(user !=null){
			sql+= " where ";
		if(user.getU_name()!=null ){
			if(user.getU_name().trim().length()>0){
				sql+=" u_name like '%"+user.getU_name()+"%' and" ;
			}
		}
	    if(user.getU_id()!=null && user.getU_id()>0){
	    	   sql+=" u_id like '%"+user.getU_id()+"%' and" ;	
		}
		if(user.getU_type()!=null && user.getU_type()>=0){
			sql+=" u_type = "+user.getU_type()+" and";
		 }
		// 去不掉最后的and
		sql = sql.substring(0, sql.length()-3);
		}
		// 可以通过NamedParameterJdbcTemplate得到JdbcTemplate
		int m = namedjdbctemp.getJdbcOperations().queryForInt(sql);
		return m;
	}
}
